/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.FeeBillDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.helper.PageHelper;
import com.minierp.model.FeeBill;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class FeeBillDaoImpl extends BaseDaoImpl implements FeeBillDao {
	public FeeBill get(String billCode) {
		String sql = "from com.minierp.model.FeeBill as obj where obj.billCode = :billCode and obj.enabled=\'Y\' order by obj.id";
		String[] paramNames = new String[]{"billCode"};
		Object[] values = new Object[]{billCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (FeeBill) list.get(0);
	}

	public List<?> getList(String searchKey, boolean examined, long tsId,
			UserBean ub, Date allowStart, int startIndex, int num)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (b.managerName like :managerName) or (b.receiverName like :receiverName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		String examineFlag = "N";
		String orderByStr = "order by b.feeDate";
		if (examined) {
			examineFlag = "Y";
			orderByStr = "order by b.feeDate desc,b.billCode desc";
		}

		String sql = "select b from com.minierp.model.FeeBill b where b.examined=:examined"
				+ str + " and b.enabled=\'Y\' " + orderByStr;
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("managerName", "%" + searchKey + "%");
				q.setString("receiverName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setString("examined", examineFlag);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getListCount(String searchKey, boolean examined, long tsId,
			UserBean ub, Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (b.managerName like :managerName) or (b.receiverName like :receiverName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "select count(b.id) from com.minierp.model.FeeBill b where b.examined=:examined"
				+ str + " and b.enabled=\'Y\'";
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("managerName", "%" + searchKey + "%");
				q.setString("receiverName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setString("examined", examineFlag);
			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List<?> getList(String searchKey, long tsId, UserBean ub,
			Date allowStart, String orderBy, int startIndex, int num)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (b.managerName like :managerName) or (b.receiverName like :receiverName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		String sql = "select b from com.minierp.model.FeeBill b where b.enabled=\'Y\'"
				+ str;
		if (orderBy != null && orderBy.equals("PAY_DATE_ASC")) {
			sql = sql + " order by b.feeDate asc,b.billCode asc";
		} else {
			sql = sql + " order by b.feeDate desc,b.billCode desc";
		}

		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("managerName", "%" + searchKey + "%");
				q.setString("receiverName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public int getListCount(String searchKey, long tsId, UserBean ub,
			Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (b.managerName like :managerName) or (b.receiverName like :receiverName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.FeeBill b where b.enabled=\'Y\'"
				+ str;
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("managerName", "%" + searchKey + "%");
				q.setString("receiverName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg11 = Integer.parseInt(list.get(0).toString());
				return arg11;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public Object[] getStatusValues(long id) {
		String sql = "select b.updateTime,b.enabled,b.examined from com.minierp.model.FeeBill b where b.id="
				+ id;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (Object[]) list.get(0) : null;
	}

	public BigDecimal getSmsCost(Date startDate, Date endDate) throws Exception {
		String sql = "select sum(b.cost) from com.minierp.model.FeeBill b where b.enabled=\'Y\' and b.examined=\'Y\' and b.feeDate>=:startDate and b.feeDate<:endDate";
		Session sess = null;

		BigDecimal arg7;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (list.isEmpty() || list.get(0) == null) {
				arg7 = new BigDecimal(0);
				return arg7;
			}

			arg7 = new BigDecimal(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg7;
	}

	public BigDecimal getCost(long bbId) {
		String sql = "select sum(i.cost) from com.minierp.model.FeeBillItem i,com.minierp.model.FeeBill b where i.billId = b.id and b.examined=\'Y\' and b.enabled=\'Y\' and i.bbId="
				+ bbId;
		List list = this.getHibernateTemplate().find(sql);
		return !list.isEmpty() && list.get(0) != null ? new BigDecimal(list
				.get(0).toString()) : new BigDecimal(0);
	}

	public List<?> getMonthCost(Date startDate, Date endDate, long tsId,
			UserBean ub) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select month(b.fee_date),sum(b.cost) from fee_bill b where b.enabled=\'Y\' and b.examined=\'Y\'"
				+ str
				+ " and b.fee_date>=:startDate"
				+ " and b.fee_date<:endDate" + " group by month(b.fee_date)";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getExamSmsCount(long[] tsIdArr) {
		if (tsIdArr != null && tsIdArr.length != 0) {
			String sql = "select b.billCode from  com.minierp.model.FeeBill b where b.examSms=\'Y\'"
					+ PageHelper.createIdArrStr(tsIdArr, "b.tallySetId", true);
			return this.getHibernateTemplate().find(sql);
		} else {
			return new ArrayList();
		}
	}

	public List getExamSmsCount() {
		String sql = "select b.billCode from  com.minierp.model.FeeBill b where b.examSms=\'Y\'";
		return this.getHibernateTemplate().find(sql);
	}

	public int updateExamSms() {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.FeeBill set examSms=\'N\' ,updateTime=:updateTime where examSms=\'Y\'";
						Query q = session.createQuery(hql);
						q.setTimestamp("updateTime", new Date());
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getListForBusinessBill(long bbId, boolean justExamined) {
		String sql = "select i,b.feeDate,b.billCode,b.managerName,b.departmentId,b.createrName,b.examined,b.createTime from com.minierp.model.FeeBill b,com.minierp.model.FeeBillItem i where b.id = i.billId and i.bbId="
				+ bbId + " and b.enabled=\'Y\'";
		if (justExamined) {
			sql = sql + " and b.examined=\'Y\'";
		}

		sql = sql + " order by b.feeDate,b.billCode";
		return this.getHibernateTemplate().find(sql);
	}

	public List getTypeCost(long tsId, UserBean ub, Date startDate, Date endDate)
			throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select sum(i.cost),i.ft_id,t.type_name,\'0\' as perc from fee_bill b inner join fee_bill_item i on i.bill_id=b.id left join fee_type t on t.id=i.ft_id where b.enabled=\'Y\' and b.examined=\'Y\' and b.fee_date>=:startDate and b.fee_date<:endDate"
				+ str + " group by i.ft_id" + " order by t.sequence_code";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getAppListCount(Date startDate, Date endDate, String receName,
			long baId, long tsId, UserBean ub) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.feeDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.feeDate<:endDate";
		}

		if (receName != null && !receName.equals("")) {
			str = str + " and (b.receiverName like :receName)";
		}

		if (baId != 0L) {
			str = str + " and b.bankAccountId=" + baId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.FeeBill b where b.enabled=\'Y\'"
				+ str;
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (receName != null && !receName.equals("")) {
				q.setString("receName", "%" + receName + "%");
			}

			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List getAppList(Date startDate, Date endDate, String receName,
			long baId, long tsId, UserBean ub, String orde, int startIndex,
			int num) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.feeDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.feeDate<:endDate";
		}

		if (receName != null && !receName.equals("")) {
			str = str + " and (b.receiverName like :receName)";
		}

		if (baId != 0L) {
			str = str + " and b.bankAccountId=" + baId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String ordeStr = " order by b.feeDate desc,b.billCode desc";
		if (orde != null) {
			if (orde.equals("FEE_DATE")) {
				ordeStr = " order by b.feeDate desc,b.billCode desc";
			} else if (orde.equals("FEE_COST")) {
				ordeStr = " order by b.cost desc,b.billCode desc";
			}
		}

		String sql = "select b from com.minierp.model.FeeBill b where b.enabled=\'Y\'"
				+ str + ordeStr;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (receName != null && !receName.equals("")) {
				q.setString("receName", "%" + receName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	public List getAppListForExam(String sk, long tsId, UserBean ub,
			Date allowStart, int startIndex, int num) throws Exception {
		String str = "";
		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (b.receiverName like :sk))";
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b from com.minierp.model.FeeBill b where b.examined=\'N\' and b.enabled=\'Y\'"
				+ str + " order by b.feeDate desc,b.billCode desc";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public List getAppListForExamCount(String sk, long tsId, UserBean ub,
			Date allowStart) throws Exception {
		String str = "";
		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (b.receiverName like :sk))";
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.FeeBill b where b.examined=\'N\' and b.enabled=\'Y\'"
				+ str;
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getAppListForUnExam(String sk, long tsId, UserBean ub,
			Date allowStart, int startIndex, int num) throws Exception {
		String str = "";
		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (b.receiverName like :sk))";
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b from com.minierp.model.FeeBill b where b.examined=\'Y\' and b.enabled=\'Y\'"
				+ str + " order by b.feeDate desc,b.billCode desc";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public int getAppListForUnExamCount(String sk, long tsId, UserBean ub,
			Date allowStart) throws Exception {
		String str = "";
		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (b.receiverName like :sk))";
		}

		if (allowStart != null) {
			str = str + " and b.feeDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*) from com.minierp.model.FeeBill b where b.examined=\'Y\' and b.enabled=\'Y\'"
				+ str;
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg11 = Integer.parseInt(list.get(0).toString());
				return arg11;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}
}